﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data;
using nvn.Core.MVP;
using Hutech.CRM.Library.Models.Entities;

namespace Hutech.CRM.Library.Models.DataAccessLayer
{
    public class AccountCustomerDAL
    {
        /// <summary>
        /// Inserts a row in the cs_AccountCustomer table. Returns rows affected count.
        /// </summary>
        /// <param name="accountCustomerGuid"> accountCustomerGuid </param>
        /// <param name="hoTen"> hoTen </param>
        /// <param name="diaChi"> diaChi </param>
        /// <param name="email"> email </param>
        /// <param name="dienThoai"> dienThoai </param>
        /// <param name="level"> level </param>
        /// <param name="fax"> fax </param>
        /// <param name="taiKhoanNganHang"> taiKhoanNganHang </param>
        /// <param name="maSoThue"> maSoThue </param>
        /// <param name="isDelete"> isDelete </param>
        /// <param name="userUpdated"> userUpdated </param>
        /// <param name="updatedDate"> updatedDate </param>
        /// <returns>int</returns>
        public static int Create(AccountCustomer item)
        {
            SqlParameterHelper sph = new SqlParameterHelper(ConnectionStringStatic.GetWriteConnectionString(), "cs_AccountCustomer_Insert", 19);
            sph.DefineSqlParameter("@AccountCustomerGuid", SqlDbType.UniqueIdentifier, ParameterDirection.Input, item.AccountCustomerGuid);
            sph.DefineSqlParameter("@HoTen", SqlDbType.NVarChar, 50, ParameterDirection.Input, item.HoTen);
            sph.DefineSqlParameter("@DiaChi", SqlDbType.NVarChar, 256, ParameterDirection.Input, item.DiaChi);
            sph.DefineSqlParameter("@Email", SqlDbType.NVarChar, 256, ParameterDirection.Input, item.Email);
            sph.DefineSqlParameter("@DienThoai", SqlDbType.NVarChar, 25, ParameterDirection.Input, item.DienThoai);
            sph.DefineSqlParameter("@Level", SqlDbType.UniqueIdentifier, ParameterDirection.Input, item.Level);
            sph.DefineSqlParameter("@Fax", SqlDbType.NVarChar, 25, ParameterDirection.Input, item.Fax);
            sph.DefineSqlParameter("@TaiKhoanNganHang", SqlDbType.NVarChar, 25, ParameterDirection.Input, item.TaiKhoanNganHang);
            sph.DefineSqlParameter("@MaSoThue", SqlDbType.NVarChar, 25, ParameterDirection.Input, item.MaSoThue);
            sph.DefineSqlParameter("@IsDelete", SqlDbType.Bit, ParameterDirection.Input, item.IsDelete);
            sph.DefineSqlParameter("@UserUpdated", SqlDbType.UniqueIdentifier, ParameterDirection.Input, item.UserUpdated);
            sph.DefineSqlParameter("@LastUpdatedDate", SqlDbType.DateTime, ParameterDirection.Input, item.LastUpdatedDate);

            sph.DefineSqlParameter("@TenCongTy", SqlDbType.NVarChar, -1, ParameterDirection.Input, item.TenCongTy);
            sph.DefineSqlParameter("@NganhNgheGuid", SqlDbType.UniqueIdentifier, ParameterDirection.Input, item.NganhNghe);
            sph.DefineSqlParameter("@ChucVuGuid", SqlDbType.UniqueIdentifier, ParameterDirection.Input, item.ChucVu);
            sph.DefineSqlParameter("@TinhThanhPhoGuid", SqlDbType.UniqueIdentifier, ParameterDirection.Input, item.TinhThanhPho);
            sph.DefineSqlParameter("@Yahoo", SqlDbType.NVarChar, -1, ParameterDirection.Input, item.Yahoo);
            sph.DefineSqlParameter("@Skype", SqlDbType.NVarChar, -1, ParameterDirection.Input, item.Skype);
            sph.DefineSqlParameter("@NgaySinh", SqlDbType.DateTime, ParameterDirection.Input, item.NgaySinh);
            int rowsAffected = sph.ExecuteNonQuery();
            return rowsAffected;

        }


        /// <summary>
        /// Updates a row in the cs_AccountCustomer table. Returns true if row updated.
        /// </summary>
        /// <param name="accountCustomerGuid"> accountCustomerGuid </param>
        /// <param name="hoTen"> hoTen </param>
        /// <param name="diaChi"> diaChi </param>
        /// <param name="email"> email </param>
        /// <param name="dienThoai"> dienThoai </param>
        /// <param name="level"> level </param>
        /// <param name="fax"> fax </param>
        /// <param name="taiKhoanNganHang"> taiKhoanNganHang </param>
        /// <param name="maSoThue"> maSoThue </param>
        /// <param name="isDelete"> isDelete </param>
        /// <param name="userUpdated"> userUpdated </param>
        /// <param name="updatedDate"> updatedDate </param>
        /// <returns>bool</returns>
        public static bool Update(AccountCustomer item)
        {
            SqlParameterHelper sph = new SqlParameterHelper(ConnectionStringStatic.GetWriteConnectionString(), "cs_AccountCustomer_Update", 19);
            sph.DefineSqlParameter("@AccountCustomerGuid", SqlDbType.UniqueIdentifier, ParameterDirection.Input, item.AccountCustomerGuid);
            sph.DefineSqlParameter("@HoTen", SqlDbType.NVarChar, 50, ParameterDirection.Input, item.HoTen);
            sph.DefineSqlParameter("@DiaChi", SqlDbType.NVarChar, 256, ParameterDirection.Input, item.DiaChi);
            sph.DefineSqlParameter("@Email", SqlDbType.NVarChar, 256, ParameterDirection.Input, item.Email);
            sph.DefineSqlParameter("@DienThoai", SqlDbType.NVarChar, 25, ParameterDirection.Input, item.DienThoai);
            sph.DefineSqlParameter("@Level", SqlDbType.UniqueIdentifier, ParameterDirection.Input, item.Level);
            sph.DefineSqlParameter("@Fax", SqlDbType.NVarChar, 25, ParameterDirection.Input, item.Fax);
            sph.DefineSqlParameter("@TaiKhoanNganHang", SqlDbType.NVarChar, 25, ParameterDirection.Input, item.TaiKhoanNganHang);
            sph.DefineSqlParameter("@MaSoThue", SqlDbType.NVarChar, 25, ParameterDirection.Input, item.MaSoThue);
            sph.DefineSqlParameter("@IsDelete", SqlDbType.Bit, ParameterDirection.Input, item.IsDelete);
            sph.DefineSqlParameter("@UserUpdated", SqlDbType.UniqueIdentifier, ParameterDirection.Input, item.UserUpdated);
            sph.DefineSqlParameter("@LastUpdatedDate", SqlDbType.DateTime, ParameterDirection.Input, item.LastUpdatedDate);

            sph.DefineSqlParameter("@TenCongTy", SqlDbType.NVarChar, 256, ParameterDirection.Input, item.TenCongTy);
            sph.DefineSqlParameter("@NganhNgheGuid", SqlDbType.UniqueIdentifier, ParameterDirection.Input, item.NganhNghe);
            sph.DefineSqlParameter("@ChucVuGuid", SqlDbType.UniqueIdentifier, ParameterDirection.Input, item.ChucVu);
            sph.DefineSqlParameter("@TinhThanhPhoGuid", SqlDbType.UniqueIdentifier, ParameterDirection.Input, item.TinhThanhPho);
            sph.DefineSqlParameter("@Yahoo", SqlDbType.NVarChar, 256, ParameterDirection.Input, item.Yahoo);
            sph.DefineSqlParameter("@Skype", SqlDbType.NVarChar, 256, ParameterDirection.Input, item.Skype);
            sph.DefineSqlParameter("@NgaySinh", SqlDbType.DateTime, ParameterDirection.Input, item.NgaySinh);
            int rowsAffected = sph.ExecuteNonQuery();
            return (rowsAffected > 0);

        }

        /// <summary>
        /// Deletes a row from the cs_AccountCustomer table. Returns true if row deleted.
        /// </summary>
        /// <param name="accountCustomerGuid"> accountCustomerGuid </param>
        /// <returns>bool</returns>
        public static bool Delete(
            Guid accountCustomerGuid)
        {
            SqlParameterHelper sph = new SqlParameterHelper(ConnectionStringStatic.GetReadConnectionString(), "cs_AccountCustomer_Delete", 1);
            sph.DefineSqlParameter("@AccountCustomerGuid", SqlDbType.UniqueIdentifier, ParameterDirection.Input, accountCustomerGuid);
            int rowsAffected = sph.ExecuteNonQuery();
            return (rowsAffected > 0);

        }

        /// <summary>
        /// Gets an IDataReader with one row from the cs_AccountCustomer table.
        /// </summary>
        /// <param name="accountCustomerGuid"> accountCustomerGuid </param>
        public static IDataReader GetOne(
            Guid accountCustomerGuid)
        {
            SqlParameterHelper sph = new SqlParameterHelper(ConnectionStringStatic.GetReadConnectionString(), "cs_AccountCustomer_SelectOne", 1);
            sph.DefineSqlParameter("@AccountCustomerGuid", SqlDbType.UniqueIdentifier, ParameterDirection.Input, accountCustomerGuid);
            return sph.ExecuteReader();

        }

        /// <summary>
        /// Gets a count of rows in the cs_AccountCustomer table.
        /// </summary>
        public static int GetCount()
        {

            return Convert.ToInt32(SqlHelper.ExecuteScalar(
                ConnectionStringStatic.GetReadConnectionString(),
                CommandType.StoredProcedure,
                "cs_AccountCustomer_GetCount",
                null));

        }

        /// <summary>
        /// Gets an IDataReader with all rows in the cs_AccountCustomer table.
        /// </summary>
        public static IDataReader GetAll()
        {

            return SqlHelper.ExecuteReader(
                ConnectionStringStatic.GetReadConnectionString(),
                CommandType.StoredProcedure,
                "cs_AccountCustomer_SelectAll",
                null);

        }

        /// <summary>
        /// Gets a page of data from the cs_AccountCustomer table.
        /// </summary>
        /// <param name="pageNumber">The page number.</param>
        /// <param name="pageSize">Size of the page.</param>
        /// <param name="totalPages">total pages</param>
        public static IDataReader GetPage(
            int pageNumber,
            int pageSize,
            out int totalPages)
        {
            totalPages = 1;
            int totalRows
                = GetCount();

            if (pageSize > 0) totalPages = totalRows / pageSize;

            if (totalRows <= pageSize)
            {
                totalPages = 1;
            }
            else
            {
                int remainder;
                Math.DivRem(totalRows, pageSize, out remainder);
                if (remainder > 0)
                {
                    totalPages += 1;
                }
            }
            //Chu' y' add them cai nay trong control phan trang
            if (totalPages < pageNumber) pageNumber = totalPages;

            SqlParameterHelper sph = new SqlParameterHelper(ConnectionStringStatic.GetReadConnectionString(), "cs_AccountCustomer_SelectPage", 2);
            sph.DefineSqlParameter("@PageNumber", SqlDbType.Int, ParameterDirection.Input, pageNumber);
            sph.DefineSqlParameter("@PageSize", SqlDbType.Int, ParameterDirection.Input, pageSize);
            return sph.ExecuteReader();

        }

        #region Add by tsandtm
        public static int GetCountForSearch(
            string kindSearch,
            string keySearch,
            Guid Job,
            Guid Position,
            Guid Province,
            Guid Level)
        {

            SqlParameterHelper sph = new SqlParameterHelper(ConnectionStringStatic.GetReadConnectionString(), "cs_AccountCustomer_GetCountForSearch", 6);
            sph.DefineSqlParameter("@KindSearch", SqlDbType.NVarChar, 128, ParameterDirection.Input, kindSearch);
            sph.DefineSqlParameter("@KeySearch", SqlDbType.NVarChar, 256, ParameterDirection.Input, keySearch);
            sph.DefineSqlParameter("@Job", SqlDbType.UniqueIdentifier, ParameterDirection.Input, Job);
            sph.DefineSqlParameter("@Position", SqlDbType.UniqueIdentifier, ParameterDirection.Input, Position);
            sph.DefineSqlParameter("@Province", SqlDbType.UniqueIdentifier, ParameterDirection.Input, Province);
            sph.DefineSqlParameter("@Level", SqlDbType.UniqueIdentifier, ParameterDirection.Input, Level);
            return Convert.ToInt32(sph.ExecuteScalar());

        }
        public static IDataReader GetPageForSearch(
            string kindSearch,
            string keySearch,
            Guid Job,
            Guid Position,
            Guid Province,
            Guid Level,
            int pageNumber,
            int pageSize,
            out int totalPages)
        {
            totalPages = 1;
            int totalRows
                = GetCountForSearch(
                    kindSearch,
                    keySearch,
                    Job,
                    Position,
                    Province,
                    Level
                    );

            if (pageSize > 0) totalPages = totalRows / pageSize;

            if (totalRows <= pageSize)
            {
                totalPages = 1;
            }
            else
            {
                int remainder;
                Math.DivRem(totalRows, pageSize, out remainder);
                if (remainder > 0)
                {
                    totalPages += 1;
                }
            }
            //Chu' y' add them cai nay trong control phan trang
            if (totalPages < pageNumber) pageNumber = totalPages;

            SqlParameterHelper sph = new SqlParameterHelper(ConnectionStringStatic.GetReadConnectionString(), "cs_AccountCustomer_SearchForFilter", 8);
            sph.DefineSqlParameter("@KindSearch", SqlDbType.NVarChar,128, ParameterDirection.Input, kindSearch);
            sph.DefineSqlParameter("@KeySearch", SqlDbType.NVarChar,256, ParameterDirection.Input, keySearch);
            sph.DefineSqlParameter("@Job", SqlDbType.UniqueIdentifier, ParameterDirection.Input, Job);
            sph.DefineSqlParameter("@Position", SqlDbType.UniqueIdentifier, ParameterDirection.Input, Position);
            sph.DefineSqlParameter("@Province", SqlDbType.UniqueIdentifier, ParameterDirection.Input, Province);
            sph.DefineSqlParameter("@Level", SqlDbType.UniqueIdentifier, ParameterDirection.Input, Level);
            sph.DefineSqlParameter("@PageNumber", SqlDbType.Int, ParameterDirection.Input, pageNumber);
            sph.DefineSqlParameter("@PageSize", SqlDbType.Int, ParameterDirection.Input, pageSize);
            return sph.ExecuteReader();

        }

        #endregion


    }

}



